/* This program plots the ROC curves of jointly rated bonds.                                        */

libname libone 'C:\Users\m40lxz1\OneDrive - Northern Illinois University\quality\sas\sasdata\';

/* The following steps input the annual rating and default data.  Change the input rating data for 
different CRA */


proc transpose data=libone.annual_MDY_rating out=rating1; 
 by id;

data rating1;
 set rating1;
 year=substr(_Name_, 4,4)*1;
 rename col1=rating1;

data rating1;
 set rating1;
 if rating1=. then delete;
if year ge 2014 and year le 2020;
 *if year ge 2014 and year le 2018;
 keep year id rating1;



proc transpose data=libone.annual_Fitch_rating out=rating2; 
 by id;

data rating2;
 set rating2;
 year=substr(_Name_, 4,4)*1;
 rename col1=rating2;

data rating2;
 set rating2;
 if rating2=. then delete;
 if year ge 2014 and year le 2020;
* if year ge 2014 and year le 2018;
 keep year id rating2;

proc sql;
 create table rating as 
 select A.*, B.rating2
 from rating1 as A
 inner join rating2 as B
 on A.id=B.id and A.year=B.year;

run;


proc sql;
 create table list as
 select A.*, B.earliest
 from rating as A
 left join libone.defaultlist_2023 as B
 on A.id=B.id;

data list;
 set list;
 if year(earliest)-year le 0 and year(earliest)-year ge 0 then default=1; 
 else if earliest ne . and year(earliest)-year lt 0 then delete;
else default=0;

/*
proc sort data=list;
 by rating1;

proc freq data=list;
 table default;
 by rating1;

data check;
 set list;
 if default=1 and rating2=8;

proc sort data=check;
 by id;

proc print data=check;
 var rating1 rating2 default id year earliest;run;

proc freq data=check;
 table id;run;

data check3;
set list;
 keep id earliest;
 if default=1;

proc sort data=check3 nodup;
 by id;run;

run;
proc sort data=list;
 by id;run;*/

proc sort data=list;
 by default;run;

proc freq data=list;
 table default;run;

proc sort data=list;
 by default rating1;

proc freq data=list noprint;
 table rating1 /out=freq1 outcum;
 by default; run;

data freq1;
 set freq1;
 rename rating1=rating;
 rename percent=percent1; 
 keep default rating1 percent ;						* percent is the percentage of defaulted or surviving bonds at each rating category;

proc sort data=list;
 by default rating2;

proc freq data=list noprint;
table rating2 /out=freq2 outcum;
by default;

proc means data=list;
 var default;run;

data freq2;
 set freq2;
 rename rating2=rating;
 rename percent=percent2;
 keep default rating2 percent;

proc sort data=freq1;
 by default rating;

proc sort data=freq2;
 by default rating;

data freq;
 merge freq1 freq2;
  by default rating;run;


data ratinglist;
 set freq; 
 if default=0;
 keep rating;

data default;
 set freq;
 if default=1;

data default_new;
 merge default ratinglist;
by rating;run;

data default_new;
 set default_new;
 default=1;
 if percent1=. then percent1=0;
 if percent2=. then percent2=0;

proc sort data=default_new;
 by descending rating;

data default_new;
 set default_new;
 retain cum_def1 cum_def2;
 if _N_=1 then do; 
cum_def1=percent1;
cum_def2=percent2;
end;
 else do;
cum_def1=cum_def1+percent1;
cum_def2=cum_def2+percent2;
end;run;

data survive;
 set freq;
  if default=0;

data survive_new;
merge survive ratinglist;
by rating;

data survive_new;
 set survive_new;
 default=0;
 if percent1=. then percent1=0;
 if percent2=. then percent2=0;

proc sort data=survive_new;
 by descending rating;

data survive_new;
 set survive_new;
 retain cum_survive1 cum_survive2;
 if _N_=1 then do;
cum_survive1=percent1;
cum_survive2=percent2;
end;
 else do;
cum_survive1=cum_survive1+percent1;
cum_survive2=cum_survive2+percent2;
end;

run;

proc sort data=survive_new;
 by  rating;

proc sort data=default_new;
 by rating;

data dist;
 merge survive_new default_new;
 by rating;
 keep rating cum_survive1 cum_survive2 cum_def1 cum_def2;run;

data dist;
 set dist;
 if rating=1 then do; cr='AAA '; cr_m='Aaa '; end;
 else if rating=2 then do; cr='AA+ '; cr_m='Aa1 '; end;
 else if rating=3 then do;  cr='AA  '; cr_m='Aa2 '; end;
 else if rating=4 then do; cr='AA- '; cr_m='Aa3 '; end;
 else if rating=5 then do; cr='A+  '; cr_m='A1  '; end;
 else if rating=6 then do; cr='A   '; cr_m='A2  '; end; 
 else if rating=7 then do; cr='A-  '; cr_m='A3  '; end; 
 else if rating=8 then do; cr='BBB+'; cr_m='Baa1'; end;
 else if rating=9 then do; cr='BBB '; cr_m='Baa2'; end;
 else if rating=10 then do; cr='BBB-'; cr_m='Baa3'; end;
 else if rating=11 then do; cr='BB+ '; cr_m='Ba1 '; end;
 else if rating=12 then do; cr='BB  '; cr_m='Ba2 '; end;
 else if rating=13 then do; cr='BB- '; cr_m='Ba3 '; end;
 else if rating=14 then do; cr='B+  '; cr_m='B1  '; end;
 else if rating=15 then do; cr='B   '; cr_m='B2  '; end;
 else if rating=16 then do; cr='B-  '; cr_m='B3  '; end;
 else if rating=17 then do; cr='CCC+'; cr_m='Caa1'; end;
 else if rating=18 then do; cr='CCC'; cr_m='Caa2'; end;
 else if rating=19 then do; cr='CCC-'; cr_m='Caa3'; end;
 else if rating=20 then do; cr='CC  '; cr_m='Ca  '; end;
 else if rating=21 then do cr='C  '; cr_m='C  '; end;
 cum_survive1=cum_survive1/100;
 cum_survive2=cum_survive2/100;
 cum_def1=cum_def1/100;
 cum_def2=cum_def2/100;
 run;

data dist;
 set dist end=eof;
 if eof then do;
 output;
 rating=22;
 cum_survive1=0;
 cum_survive2=0;
 cum_def1=0;
 cum_def2=0;
 cr=' ';
 cr_m=' ';
 end;
 output; run;

/*

proc sgplot data=dist;
step x=cum_survive1 y=cum_def1 /datalabel=rating markers markerattrs=(symbol=Asterisk) justify=right;
step x=cum_survive2 y=cum_def2 /datalabel=rating markers markerattrs=(symbol=circle) justify=right; 
run; 
*/

ods graphics on;
ODS html SGE=ON style=journal;

proc sgplot data=dist;
*series x=cum_survive1 y=cum_def1 /
datalabel=cr datalabelattrs=(family='Times New Roman' size=10 color=black weight=bold) legendlabel='S&P'
lineattrs=(pattern=1 color=black) markers markerattrs=(symbol=Asterisk color=black);

series x=cum_survive1 y=cum_def1 /
datalabel=cr_m datalabelattrs=(family='Times New Roman' size=10 color=black weight=bold ) legendlabel="Moody's"  
lineattrs=(pattern=2 color=black)  markers markerattrs=( symbol=circle color=black);

series x=cum_survive2 y=cum_def2 /
datalabel=cr datalabelattrs=(family='Times New Roman' size=10 color=blue ) legendlabel="Fitch"  
lineattrs=(pattern=14 color=blue)  markers markerattrs=( symbol=triangle color=blue);

*series x=cum_survive1 y=cum_def1 /
datalabel=cr datalabelattrs=(family='Times New Roman' size=10 color=black weight=bold) legendlabel="Fitch"  
lineattrs=(pattern=14 color=black)  markers markerattrs=( symbol=triangle color=black);

*series x=cum_survive2 y=cum_def2 /
datalabel=cr datalabelattrs=(family='Times New Roman' size=10 color=blue ) legendlabel="EJR"  
lineattrs=(pattern=34 color=blue)  markers markerattrs=( symbol=hash color=blue);

xaxis label='False Alarm Rate' labelattrs=(Family='Times New Roman' size=12) valueattrs = (Family='Times New Roman' size=12) max=1.1 ;
yaxis label='Default Hit Rate' labelattrs=(Family='Times New Roman' size=12) valueattrs = (Family='Times New Roman' size=12) max=1.1;
*where rating le 19;
run; 


run;


